package com.aspire.query.service.impl;

import com.aspire.query.constant.SysConstant;
import com.aspire.query.service.QueryInfo;
import com.aspire.query.util.MyConnection;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author zhouyumin
 */
@Service
@Slf4j
public class QueryInfoImpl implements QueryInfo {

    @Autowired
    private MyConnection myConnection;

    private BufferedWriter fileWriter;

    @Override
    public void query(String sql) {
        // 获取文件名称
        String csvFileName = getFileName();
        Connection connection = myConnection.getConnection();
        ResultSet resultSet;

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            // 查询结果集
            resultSet = preparedStatement.executeQuery();
            fileWriter = new BufferedWriter(new FileWriter(csvFileName));
            // 获取表头字段个数
            int columnCount = writeHeaderLine(resultSet);

            while (resultSet.next()) {
                String line = "";
                for (int i = 1; i <= columnCount; i++) {
                    Object object = resultSet.getObject(i);
                    String value = "";
                    if (object != null) {
                        value = object.toString();
                    }
                    line = line.concat(value);
                    // 判断是否是最后一列，如果是，不用加逗号分隔，通过newLine换行
                    if (i != columnCount) {
                        line = line.concat(SysConstant.CSV_SPILT);
                    }
                }
                //换行
                fileWriter.newLine();
                fileWriter.write(line);
            }
            preparedStatement.close();
            fileWriter.close();

        } catch (SQLException e) {
            log.error("数据库连接异常");
            e.printStackTrace();
        } catch (IOException e) {
            log.error("文件IO异常");
            e.printStackTrace();
        }
    }

    /**
     * 获取文件名称 ：csv文件以表名加日期生成（表名前缀固定为query）
     * @return
     */
    private String getFileName() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
        String dateTimeInfo = dateFormat.format(new Date());
        return SysConstant.CSV_FILE_NAME_PREFIX.concat(String.format("_%s.csv", dateTimeInfo));
    }

    /**
     * 将表的列名称写入CSV文件第一行
     * @param result
     * @return
     * @throws SQLException
     * @throws IOException
     */
    private int writeHeaderLine(ResultSet result) throws SQLException, IOException {
        ResultSetMetaData metaData = result.getMetaData();
        int columnsCount = metaData.getColumnCount();
        log.info("获取到数据库字段个数={}", columnsCount);

        String headerLine = "";
        // 遍历字段个数，追加写入表头
        for (int i = 1; i <= columnsCount; i++) {
            String columnName = metaData.getColumnName(i);
            headerLine = headerLine.concat(columnName);
            if (i != columnsCount) {
                headerLine = headerLine.concat(SysConstant.CSV_SPILT);
            }
        }
        log.info("表头数据={}",headerLine);
        fileWriter.write(headerLine);
        return columnsCount;
    }
}
